﻿Imports System.Data.SqlClient
Public Class FrmCapNhatKiemke
    Dim tbl As DataTable
    Dim dbAdapter As SqlDataAdapter
    Dim cmd As SqlCommand
    Dim tennhap = ModuleTenDangNhap.m_tennhap
    Private Sub NapPhong()
        cmd = New SqlCommand(" select * from DMPhong", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxPhong.DisplayMember = "Tenphong"
        ComboBoxPhong.ValueMember = "maPhongID"
        ComboBoxPhong.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapNgan()
        cmd = New SqlCommand(" select * from DMNgan", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxNgan.DisplayMember = "TenNgan"
        ComboBoxNgan.ValueMember = "maNganID"
        ComboBoxNgan.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapKe()
        cmd = New SqlCommand(" select * from DMKe", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxKe.DisplayMember = "TenKe"
        ComboBoxKe.ValueMember = "maKeID"
        ComboBoxKe.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapKho()
        cmd = New SqlCommand(" select * from DMKho", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxKho.DisplayMember = "TenKho"
        ComboBoxKho.ValueMember = "maKhoID"
        ComboBoxKho.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub Naphoso()
        cmd = New SqlCommand(" select * from DMHosokiemke", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxHoso.DisplayMember = "Tenloaihoso"
        ComboBoxHoso.ValueMember = "MaloaiID"
        ComboBoxHoso.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapTaiLieuKK()
        cmd = New SqlCommand(" select * from DMTaiLieuKK", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxTaiLieuKK.DisplayMember = "TenTaiLieuKK"
        ComboBoxTaiLieuKK.ValueMember = "MaTaiLieuKK"
        ComboBoxTaiLieuKK.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapDonViHuyen()
        cmd = New SqlCommand(" select * from TENHUYEN", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxHuyen.DisplayMember = "T_huyen"
        ComboBoxHuyen.ValueMember = "maH"
        ComboBoxHuyen.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapThoiHanBaoQuan()
        cmd = New SqlCommand(" select * from DMThoiHanBaoQuan", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxThoiHanBQ.DisplayMember = "ThoiHanBaoQuan"
        ComboBoxThoiHanBQ.ValueMember = "MaDMThoiHan"
        ComboBoxThoiHanBQ.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub FrmNhapKiemke_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        NapKho()
        NapKe()
        NapNgan()
        NapPhong()
        Naphoso()
        NapTaiLieuKK()
        NapDonViHuyen()
        NapThoiHanBaoQuan()



    End Sub
    Private Sub ComboBoxHuyen_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBoxHuyen.SelectedIndexChanged
        ' load ten xa
        cmd = New SqlCommand(" select * from TenXa where mahuyen = '" & ComboBoxHuyen.SelectedValue.ToString & "' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxXa.DisplayMember = "TenXa"
        ComboBoxXa.ValueMember = "maxa"
        ComboBoxXa.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub ComboBoxLoaiTaiLieu_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBoxTaiLieuKK.SelectedIndexChanged

    End Sub

    Private Sub Label10_Click(sender As Object, e As EventArgs)

    End Sub

    Private Sub ButtonThoat_Click(sender As Object, e As EventArgs) Handles ButtonThoat.Click
        Me.Close()
    End Sub
    
    Private Sub ButtonTimkiem_Click(sender As Object, e As EventArgs) Handles ButtonTimkiem.Click
        LoadTatCaHoSo()
        TextBoxNamLap.Text = ""
        
    End Sub
    Private Sub NapVaoTexBox()
        ComboBoxHoso.DataBindings.Clear()
        ComboBoxHoso.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "Tenloaihoso")
        TextBoxNamLap.DataBindings.Clear()
        TextBoxNamLap.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "NamThucHien")
        TextBoxSoHoSo.DataBindings.Clear()
        TextBoxSoHoSo.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "MahosoID")
        DateTimePicker1.DataBindings.Clear()
        DateTimePicker1.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "NgayNhapKho")
        ComboBoxPhong.DataBindings.Clear()
        ComboBoxPhong.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "TenPhong")
        ComboBoxNgan.DataBindings.Clear()
        ComboBoxNgan.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "TenNgan")
        ComboBoxKe.DataBindings.Clear()
        ComboBoxKe.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "TenKe")
        ComboBoxKho.DataBindings.Clear()
        ComboBoxKho.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "TenKho")
        ComboBoxHuyen.DataBindings.Clear()
        ComboBoxHuyen.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "T_Huyen")
        ComboBoxXa.DataBindings.Clear()
        ComboBoxXa.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "TenXa")
        ComboBoxThoiHanBQ.DataBindings.Clear()
        ComboBoxThoiHanBQ.DataBindings.Add("Text", DataGridView_hienthi.DataSource, "ThoiHanBaoQuan")
        If LayIDCapDVHC() = 1 Then
            rbTinh.Checked = True
        End If
        If LayIDCapDVHC() = 2 Then
            rbHuyen.Checked = True
        End If
        If LayIDCapDVHC() = 3 Then
            rbXa.Checked = True
        End If
    End Sub
    Private Function LayIDCapDVHC()
        Dim sql As String = ""
        Dim id As Integer
        sql = "SELECT CapDVHCID FROM HOSOKIEMKE WHERE MahosoID = @mahosoid"
        cmd = New SqlCommand(sql, ob_cnn)
        cmd.Parameters.AddWithValue("mahosoid", TextBoxSoHoSo.Text)
        ob_cnn.Open()
        id = cmd.ExecuteScalar()
        ob_cnn.Close()
        Return id
    End Function
    Private Sub QuanLySua()
        
        Try
            Dim sqlString As String = ""
            sqlString = "INSERT INTO QL_SUAHOSOTHONGKEKIEMKE VALUES(@hosokiemke, @nguoisua, @ngaysua, @noidungsua)"
            cmd = New SqlCommand(sqlString, ob_cnn)
            cmd.Parameters.AddWithValue("@hosokiemke", TextBoxSoHoSo.Text)
            cmd.Parameters.AddWithValue("@nguoisua", tennhap)
            cmd.Parameters.AddWithValue("@ngaysua", DateTime.Now)
            cmd.Parameters.AddWithValue("@noidungsua", " Sửa thông tin hồ sơ thống kê kiểm kê")
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub XoaHoSo()
        Try
            Dim sqlString As String = ""
            sqlString = "DELETE HOSOKIEMKE WHERE MahosoID = @mahoso"
            cmd = New SqlCommand(sqlString, ob_cnn)
            cmd.Parameters.AddWithValue("@mahoso", TextBoxSoHoSo.Text)
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
    Private Sub QuanLyXoa()
        Try
           
            Dim sqlString As String = ""
            sqlString = "INSERT INTO QL_XOAHOSOTHONGKEKIEMKE VALUES (@hosothongkekiemke, @nguoixoa, @ngayxoa)"
            cmd = New SqlCommand(sqlString, ob_cnn)
            cmd.Parameters.AddWithValue("@hosothongkekiemke", TextBoxSoHoSo.Text)
            cmd.Parameters.AddWithValue("@nguoixoa", tennhap)
            cmd.Parameters.AddWithValue("@ngayxoa", DateTime.Now)
            cmd.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        MsgBox("Xóa thành công!")
    End Sub
    Private Sub SuaThongTin(ByVal capdvhc As Integer)
        Try
            Dim sqlString As String = ""
            sqlString = "UPDATE HOSOKIEMKE SET TenQuyen = @tenquyen, MaxaId = @maxaid, MaHuyenID = @mahuyenid, MaKhoId = @makhoid, MaKeId = @makeid, MaNganId = @manganid, MaPhongId = @maphongid, NamThucHien = @namthuchien, MaLoaiHSID =@maloaihosoid, MaDMThoiHanID = @thoihanID, CapDVHCID = @capdvhcid " &
                  "WHERE MahosoID = @mahosoid"
            cmd = New SqlCommand(sqlString, ob_cnn)
            cmd.Parameters.AddWithValue("@tenquyen", ComboBoxHoso.SelectedValue)
            cmd.Parameters.AddWithValue("@maxaid", ComboBoxXa.SelectedValue)
            cmd.Parameters.AddWithValue("@mahuyenid", ComboBoxHuyen.SelectedValue)
            cmd.Parameters.AddWithValue("@makhoid", ComboBoxKho.SelectedValue)
            cmd.Parameters.AddWithValue("@makeid", ComboBoxKe.SelectedValue)
            cmd.Parameters.AddWithValue("@manganid", ComboBoxNgan.SelectedValue)
            cmd.Parameters.AddWithValue("@maphongid", ComboBoxPhong.SelectedValue)
            cmd.Parameters.AddWithValue("@namthuchien", TextBoxNamLap.Text)
            cmd.Parameters.AddWithValue("@maloaihosoid", ComboBoxTaiLieuKK.SelectedValue)
            cmd.Parameters.AddWithValue("@capdvhcid", capdvhc)
            cmd.Parameters.AddWithValue("@thoihanID", ComboBoxThoiHanBQ.SelectedValue)
            cmd.Parameters.AddWithValue("@mahosoid", TextBoxSoHoSo.Text)
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.ToString)
            Return
        End Try
    End Sub
    Private Sub LoadHoSoDaSua()

        cmd = New SqlCommand("SELECT hskk.NgayNhapKho, hskk.NamThucHien, dmtlkk.TenTaiLieuKK, dmhskk.Tenloaihoso, dvhc.TenCapDVHC, thbq.ThoiHanBaoQuan, kho.TenKho, ke.TenKe, ngan.TenNgan, phong.TenPhong, tinhtrang.TenTinhTrang, huyen.T_Huyen, xa.TenXa, hskk.MahosoID " &
                                "FROM HOSOKIEMKE hskk, DMTailieuKK dmtlkk, DMHosokiemke dmhskk, CAPDONVIHANHCHINH dvhc, DMThoiHanBaoQuan thbq, TENXA xa, TENHUYEN huyen, DMKho kho, DMKe ke, DMNgan ngan, DMPhong phong, DMTinhTrang tinhtrang " &
                                "WHERE hskk.CapDVHCID = dvhc.CapDVHCID and hskk.MaLoaiHSID = dmtlkk.MaTaiLieuKK AND hskk.TenQuyen = dmhskk.MaloaiID AND hskk.MaTinhTrangId = tinhtrang.MaTinhTrangID AND hskk.MaDMThoiHanID = thbq.MaDMThoiHan And hskk.MaxaId = xa.MaXa And hskk.MaHuyenID = huyen.maH And hskk.MaKhoId = kho.maKhoID And hskk.MaKeId = ke.MaKeID AND hskk.MaPhongId = phong.MaPhongID and hskk.MaNganId = ngan.MaNganID " &
                                "AND hskk.TenQuyen = '" & ComboBoxHoso.SelectedValue & "' " &
                                "AND hskk.MaLoaiHSID = '" & ComboBoxTaiLieuKK.SelectedValue & "' " &
                                "AND hskk.MaHuyenID = '" & ComboBoxHuyen.SelectedValue & "' " &
                                "AND hskk.MaxaId = '" & ComboBoxXa.SelectedValue & "' " &
                                "AND hskk.MahosoID = '" & TextBoxSoHoSo.Text & "'" &
                                "AND hskk.NamThucHien = '" & TextBoxNamLap.Text & "'", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridView_hienthi.DataSource = tbl
        If tbl.Rows.Count = 0 Then
            MessageBox.Show("Không tìm thấy thông tin ! ")
        End If
        ob_cnn.Close()
    End Sub
    Private Sub LoadTatCaHoSo()
        cmd = New SqlCommand("SELECT hskk.NgayNhapKho, hskk.NamThucHien, dmtlkk.TenTaiLieuKK, dmhskk.Tenloaihoso, dvhc.TenCapDVHC, thbq.ThoiHanBaoQuan, kho.TenKho, ke.TenKe, ngan.TenNgan, phong.TenPhong, tinhtrang.TenTinhTrang, huyen.T_Huyen, xa.TenXa, hskk.MahosoID " &
                                "FROM HOSOKIEMKE hskk, DMTailieuKK dmtlkk, DMHosokiemke dmhskk, CAPDONVIHANHCHINH dvhc, DMThoiHanBaoQuan thbq, TENXA xa, TENHUYEN huyen, DMKho kho, DMKe ke, DMNgan ngan, DMPhong phong, DMTinhTrang tinhtrang " &
                                "WHERE hskk.CapDVHCID = dvhc.CapDVHCID and hskk.MaLoaiHSID = dmtlkk.MaTaiLieuKK AND hskk.TenQuyen = dmhskk.MaloaiID AND hskk.MaTinhTrangId = tinhtrang.MaTinhTrangID AND hskk.MaDMThoiHanID = thbq.MaDMThoiHan And hskk.MaxaId = xa.MaXa And hskk.MaHuyenID = huyen.maH And hskk.MaKhoId = kho.maKhoID And hskk.MaKeId = ke.MaKeID AND hskk.MaPhongId = phong.MaPhongID and hskk.MaNganId = ngan.MaNganID " &
                                "AND hskk.NamThucHien like '%" & TextBoxNamLap.Text & "%' order by ngayNhapKho", ob_cnn)

        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridView_hienthi.DataSource = tbl
        If tbl.Rows.Count = 0 Then
            MessageBox.Show("Không tìm thấy thông tin ! ")
        End If
        ob_cnn.Close()
    End Sub
    Private Sub ButtonSua_Click(sender As Object, e As EventArgs) Handles ButtonSua.Click
        ob_cnn.Open()
        QuanLySua()
        If rbTinh.Checked Then
            SuaThongTin(1)
        End If
        If rbHuyen.Checked Then
            SuaThongTin(2)
        End If
        If rbXa.Checked Then
            SuaThongTin(3)
        End If
        MessageBox.Show("Thao tác thành công !")
        LoadHoSoDaSua()
        ob_cnn.Close()
    End Sub

    Private Sub LamTuoi()
        TextBoxSoHoSo.Text = ""
        TextBoxNamLap.Text = ""
    End Sub
    Private Sub ButtonXoa_Click(sender As Object, e As EventArgs) Handles ButtonXoa.Click
        If TextBoxSoHoSo.Text = Nothing Then
            MessageBox.Show("Chọn hồ sơ để xóa !")
            Return
        End If
        ob_cnn.Open()
        QuanLyXoa()
        XoaHoSo()
        LoadTatCaHoSo()
        ob_cnn.Close()
    End Sub
    Private Sub rbTinh_CheckedChanged(sender As Object, e As EventArgs) Handles rbTinh.CheckedChanged
        If rbTinh.Checked Then
            ComboBoxHuyen.Enabled = False
            ComboBoxXa.Enabled = False
        End If
    End Sub

    Private Sub rbHuyen_CheckedChanged(sender As Object, e As EventArgs) Handles rbHuyen.CheckedChanged
        If rbHuyen.Checked Then
            ComboBoxXa.Enabled = False
            ComboBoxHuyen.Enabled = True
        End If
    End Sub
    Private Sub rbXa_CheckedChanged(sender As Object, e As EventArgs) Handles rbXa.CheckedChanged
        If rbXa.Checked Then
            ComboBoxHuyen.Enabled = True
            ComboBoxXa.Enabled = True
        End If
    End Sub

    Private Sub DataGridView_hienthi_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView_hienthi.CellClick
        If e.RowIndex = -1 Then
            Return
        End If
        NapVaoTexBox()
    End Sub

    Private Sub TextBoxNamLap_TextChanged(sender As Object, e As EventArgs) Handles TextBoxNamLap.TextChanged

    End Sub

    Private Sub Label2_Click(sender As Object, e As EventArgs) Handles Label2.Click

    End Sub

    Private Sub Panel2_Paint(sender As Object, e As PaintEventArgs) Handles Panel2.Paint

    End Sub

    Private Sub ComboBoxNgan_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBoxNgan.SelectedIndexChanged

    End Sub

    Private Sub Label6_Click(sender As Object, e As EventArgs) Handles Label6.Click

    End Sub

    Private Sub ComboBoxPhong_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBoxPhong.SelectedIndexChanged

    End Sub

    Private Sub Label9_Click(sender As Object, e As EventArgs) Handles Label9.Click

    End Sub
End Class